import logging
from google.appengine.api import rdbms
import datetime
import operator

_INSTANCE_NAME = 'google.com:creativetools:curation-tool'
_DATABASE = 'WhiteLabelCuration'


class SQLConnector(object):
  def __init__(self, instance=_INSTANCE_NAME, database=_DATABASE):
    self.conn = rdbms.connect(instance=instance, database=database)
    try:
      self.cursor = self.conn.cursor(rdbms.cursors.DictCursor)
      self.use_utf8 = False
    except:
      self.cursor = self.conn.cursor(use_dict_cursor=True)
      self.use_utf8 = True

  def InsertData(self, data_dicts, table):
    """Insert a single row into MySQL table.

    Args:
      data_dicts: A key, value pair to specify column, value to insert.
      table: The MySQL table name.
    """
    sql = 'INSERT INTO %s ' % table
    field_list = []
    value_list = []
    for field, value in data_dicts.iteritems():
      field_list.append('%s' % field)
      if type(value).__name__ == 'str' or type(value).__name__ == 'unicode':
        value = value.replace('"', '""')
        value = value.replace('\\', '\\\\')
        value_list.append('"%s"' % unicode(value))
      else:
        value_list.append('%s' % value)
    sql += '(%s) VALUES (%s)' % (','.join(field_list),','.join(value_list))
    logging.info(sql)
    self.cursor.execute(sql)
    self.conn.commit()
    return

  def UpdateTable(self, set_values, where_filters, table):
    """Updates the MYSQL table field(s) with data values.

    Args:
      set_values: A key, value pair to specify column name, value.
      where_filters: A key, value pair to specifiy column, value filter.
      table: The MySQL table name to modify.
    """
    sql = ("""
           UPDATE %s
           SET %s
           WHERE %s
           """)

    set_sql = []
    for column, value in set_values.iteritems():
      if type(value).__name__ == 'str' or type(value).__name__ == 'unicode':
        value = value.replace('"', '""')
        value = value.replace('\\', '\\\\')
      set_sql.append('%s = "%s"' % (column, value))

    where_sql = []
    for column, value in where_filters.iteritems():
      where_sql.append('%s = "%s"' % (column, value))

    sql = sql % (table, ','.join(set_sql), ' AND '.join(where_sql))
    logging.info(sql)
    self.cursor.execute(sql)
    self.conn.commit()
    return


  def GetTableData(self, table, filters=None, order_by=None):
    """Fetch all table rows matching filter criteria.

    Args:
      table: Name of table to fetch data from.
      filters: A set of column value pairs to filter results.
      order_by: Tuple containing (property_name, 'DESC|ASC')

    Returns:
      List of elements where column and values are mapped to elements.
    """
    sql = 'SELECT * FROM %s ' % table
    where_sql = []
    if filters:
      for column, value in filters.iteritems():
        where_sql.append('%s = "%s"' % (column, value))
      sql += 'WHERE %s' % ' AND '.join(where_sql)

    if order_by:
      sql += 'ORDER BY %s %s' % (order_by[0], order_by[1])
    return self._Execute(sql)


  def _Execute(self, sql):
    logging.info(sql)
    if self.use_utf8:
      self.cursor.execute('SET NAMES utf8');
    else:
      self.cursor.execute('SET NAMES ASCII');

    self.cursor.execute(sql)
    return self.cursor.fetchall()

  def DeleteData(self, data_dicts, table):
    """Delete row or rows from MySQL table.

    Args:
      data_dicts: A key,value pair that specifies column name -> value to
          filter.
      table: The table name to modify.
    """
    sql = ("""
           DELETE FROM %s
           """) % table

    where_sql = []
    for column, value in data_dicts.iteritems():
      where_sql.append('%s = "%s"' % (column, value))
    sql += ' WHERE %s' % (' AND '.join(where_sql))
    logging.info(sql)
    self.cursor.execute(sql)
    self.conn.commit()

  def Close(self):
    """Close Database connection."""
    self.conn.close()
